package excel;

import cn.hutool.core.date.DateUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.codec.binary.Base64;
import org.apache.commons.lang3.RandomUtils;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.crypto.Mac;
import javax.crypto.spec.SecretKeySpec;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;
import java.util.concurrent.TimeUnit;

/**
 * @author huangya
 * @date 2022/12/1
 * 需要的jar：
 *          <dependency>
 *             <groupId>cn.hutool</groupId>
 *             <artifactId>hutool-core</artifactId>
 *             <version>${hutool.version}</version>
 *         </dependency>
 *
 *         <dependency>
 *             <groupId>cn.hutool</groupId>
 *             <artifactId>hutool-http</artifactId>
 *             <version>${hutool.version}</version>
 *         </dependency>
 *
 *         <dependency>
 *             <groupId>cn.hutool</groupId>
 *             <artifactId>hutool-crypto</artifactId>
 *             <version>${hutool.version}</version>
 *         </dependency>
 *
 *         <dependency>
 *             <groupId>cn.hutool</groupId>
 *             <artifactId>hutool-poi</artifactId>
 *             <version>${hutool.version}</version>
 *         </dependency>
 *
 *         <dependency>
 *             <groupId>org.apache.poi</groupId>
 *             <artifactId>poi-ooxml</artifactId>
 *             <version>4.1.2</version>
 *         </dependency>
 *         <dependency>
 *             <groupId>org.apache.poi</groupId>
 *             <artifactId>poi</artifactId>
 *             <version>4.1.2</version>
 *         </dependency>
 */
public class HutoolExcel {

    protected static final String USER_ID_KEY = "openId";
    protected static final String USER_TOKEN_KEY = "accessToken";
    protected static final String PROXY_TOKEN_KEY = "proxyToken";
    protected static final String DEFAULT_ENCODING = "UTF-8";
    protected static final String RESULT_KEY_CODE = "code";
    protected static final String RESULT_KEY_DATA = "data";

    public static final Integer CONNECT_TIMEOUT = 500;
    public static final Integer READ_TIMEOUT = 1600;

    public  static final String BIFROST_HEADER_TAG_KEY = "__bifrost_headers__";

    private final static String QUERY_ZID = "/gw/account-base/device-vin/get-by-vin/v1";
    private final static String POI_SEARCH = "/gw/map-poi-search/getsyncstoredata/v1";

    private final static String host = "https://bm-bifrost.ebanma.com";
    private final static String userKey = "alios";
    private final static String userSecret = "aee7fb8198ca4c748af4734f20a9b048";
    private final static String userKeyBak = "algoses";
    private final static String userSecretBak = "39a3d3ae0b1a4835b068227c38b0e07a";
    private final static String bifrostHeaders = "";

    public static String invokeBifrostService(String host, String apiUrl, String userKey,
                                              String userSecret, JSONObject params,String bifrostHeaders) throws Exception {

        /**
         * "__bifrost_headers__"参数不为空则将其中的kv 加入header中
         */
        JSONObject addToHeaders = new JSONObject();
        try {
            if (org.apache.commons.lang3.StringUtils.isNotEmpty(bifrostHeaders)) {
                addToHeaders = JSON.parseObject(bifrostHeaders);
            }
        }catch(Exception e){
            System.out.println("__bifrost_headers__ format error,data:"+bifrostHeaders);
        }

        //获取参数签名
        String reqParams="";
        if(params!=null) {
            reqParams= params.toJSONString();
        }

        long timestamp = System.currentTimeMillis();
        String signData = apiUrl + timestamp + userKey + reqParams;
        String sign = genHmacSign(signData, userSecret);

        //获取header
        Map<String, String> header = new HashMap<>();
        header.put("gw-user-key", userKey);
        header.put("gw-timestamp", String.valueOf(timestamp));
        header.put("gw-sign", sign);
        if (!addToHeaders.isEmpty()){
            for (String key : addToHeaders.keySet()){
                header.put(key,addToHeaders.getString(key));
            }
        }

        String requestPath = host + apiUrl;
        String result= null;
        //todo.. 数据处理

        return result;
    }

    public static String genHmacSign(String data, String key) {
        try {
            SecretKeySpec signinKey = new SecretKeySpec(key.getBytes(), "HmacSHA1");
            Mac mac = Mac.getInstance("HmacSHA1");
            mac.init(signinKey);
            byte[] rawHmac = mac.doFinal(data.getBytes(DEFAULT_ENCODING));
            byte[] rawData = Base64.encodeBase64URLSafe(rawHmac);
            return new String(rawData, DEFAULT_ENCODING);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public static JSONObject queryPoiInfoByVin(String vin, String poi_id){
        JSONObject params = new JSONObject();
        params.put("vin", vin);

        JSONObject result = new JSONObject();
        result.put("is_home", "false");
        result.put("is_company", "false");
        result.put("is_collection", "false");

        String zid = null;
        if(!StringUtils.isEmpty(vin)){
            zid = queryZidByVin(vin);

            /************************ 查询poi信息 ************************/
            if(!StringUtils.isEmpty(zid)){
                String is_home = queryIsHome(zid, poi_id);
                String is_company = queryIsCompany(zid, poi_id);
                String is_collection = queryIsCollection(zid, poi_id);
                result.put("is_home", is_home);
                result.put("is_company", is_company);
                result.put("is_collection", is_collection);
            }
        }

        result.put("vin", vin);
        result.put("zid", zid);

        return result;
    }

    public static String queryZidByVin(String vin){
        JSONObject params = new JSONObject();
        params.put("vin", vin);

        String zid = null;
        if(!StringUtils.isEmpty(vin)) {
            String zidResponse = null;

            /************************ 查询zid ************************/
            try {
                zidResponse = invokeBifrostService(host, QUERY_ZID, userKey, userSecret, params, bifrostHeaders);
            } catch (Exception e) {
                System.out.println("===================");
                e.printStackTrace();
            }
            JSONObject zidJson = JSON.parseObject(zidResponse);
            if (zidJson != null && Objects.equals(zidJson.getInteger("code"), 0)) {
                JSONObject zidData = zidJson.getJSONObject("data");
                if (zidData != null) {
                    zid = zidData.getString("bmAccountId");
                }
            }
        }
        return zid;
    }

    public static String queryIsHome(String zid, String poi_id){
        String is_home = "false";

        /************************ 查询poi信息 ************************/
        if(!StringUtils.isEmpty(zid)){
            JSONObject params = new JSONObject();
            params.put("zid", zid);
            params.put("dataPath", "/map/HomeAddress");
            params.put("keyword", "");

            String poiResponse = null;
            try {
                poiResponse = invokeBifrostService(host, POI_SEARCH, userKeyBak, userSecretBak, params, bifrostHeaders);
            } catch (Exception e) {
                System.out.println("===================");
                e.printStackTrace();
            }
            JSONObject poiJson = JSON.parseObject(poiResponse);
            if(poiJson!=null && Objects.equals(poiJson.getInteger("code"), 0)){
                JSONObject poiData = poiJson.getJSONObject("data");
                if(!CollectionUtils.isEmpty(poiData)){
                    Set<Map.Entry<String, Object>> entries = poiData.entrySet();
                    for (Map.Entry<String, Object> entry : entries) {
                        Object value = entry.getValue();
                        if(value instanceof Map){
                            String poiId = (String)((Map<?, ?>) value).get("poiId");
                            if(Objects.equals(poi_id, poiId)){
                                is_home = "true";
                            }
                        }else if(value instanceof String){
                            String valueStr = (String) value;
                            JSONObject valueJson = JSON.parseObject(valueStr);
                            String poiId = valueJson.getString("poiId");
                            if(Objects.equals(poi_id, poiId)){
                                is_home = "true";
                            }
                        }
                    }
                }
            }
        }

        return is_home;
    }

    public static String queryIsCompany(String zid, String poi_id){
        String is_company = "false";

        /************************ 查询poi信息 ************************/
        if(!StringUtils.isEmpty(zid)){
            JSONObject params = new JSONObject();
            params.put("zid", zid);
            params.put("dataPath", "/map/CompanyAddress");
            params.put("keyword", "");

            String poiResponse = null;
            try {
                poiResponse = invokeBifrostService(host, POI_SEARCH, userKeyBak, userSecretBak, params, bifrostHeaders);
            } catch (Exception e) {
                System.out.println("===================");
                e.printStackTrace();
            }
            JSONObject poiJson = JSON.parseObject(poiResponse);
            if(poiJson!=null && Objects.equals(poiJson.getInteger("code"), 0)){
                JSONObject poiData = poiJson.getJSONObject("data");
                if(!CollectionUtils.isEmpty(poiData)){
                    Set<Map.Entry<String, Object>> entries = poiData.entrySet();
                    for (Map.Entry<String, Object> entry : entries) {
                        Object value = entry.getValue();
                        if(value instanceof Map){
                            String poiId = (String)((Map<?, ?>) value).get("poiId");
                            if(Objects.equals(poi_id, poiId)){
                                is_company = "true";
                            }
                        }else if(value instanceof String){
                            String valueStr = (String) value;
                            JSONObject valueJson = JSON.parseObject(valueStr);
                            String poiId = valueJson.getString("poiId");
                            if(Objects.equals(poi_id, poiId)){
                                is_company = "true";
                            }
                        }
                    }
                }
            }
        }

        return is_company;
    }

    public static String queryIsCollection(String zid, String poi_id){
        String is_collection = "false";

        /************************ 查询poi信息 ************************/
        if(!StringUtils.isEmpty(zid)){
            JSONObject params = new JSONObject();
            params.put("zid", zid);
            params.put("dataPath", "/map/CollectionList");
            params.put("keyword", "");

            String poiResponse = null;
            try {
                poiResponse = invokeBifrostService(host, POI_SEARCH, userKeyBak, userSecretBak, params, bifrostHeaders);
            } catch (Exception e) {
                System.out.println("===================");
                e.printStackTrace();
            }
            JSONObject poiJson = JSON.parseObject(poiResponse);
            if(poiJson!=null && Objects.equals(poiJson.getInteger("code"), 0)){
                JSONObject poiData = poiJson.getJSONObject("data");
                if(!CollectionUtils.isEmpty(poiData)){
                    Set<Map.Entry<String, Object>> entries = poiData.entrySet();
                    for (Map.Entry<String, Object> entry : entries) {
                        Object value = entry.getValue();
                        if(value instanceof Map){
                            String poiId = (String)((Map<?, ?>) value).get("poiId");
                            if(Objects.equals(poi_id, poiId)){
                                is_collection = "true";
                            }
                        }else if(value instanceof String){
                            String valueStr = (String) value;
                            JSONObject valueJson = JSON.parseObject(valueStr);
                            String poiId = valueJson.getString("poiId");
                            if(Objects.equals(poi_id, poiId)){
                                is_collection = "true";
                            }
                        }
                    }
                }
            }
        }

        return is_collection;
    }

    public static List<Map<String, Object>> readExcel() {
        ExcelReader reader = ExcelUtil.getReader("D:\\软件安软包\\文档\\14、bmsearch\\多数据融合\\test1.xlsx");
        List<Map<String, Object>> maps = reader.readAll();
        return maps;
    }

    public static void writerExcel(List<Map<String, Object>> datas) throws IOException {
        ExcelWriter writer = ExcelUtil.getWriter(true);

        writer.addHeaderAlias("poiid", "poiid");
        writer.addHeaderAlias("name_chn", "name_chn");
        writer.addHeaderAlias("addr_chn", "addr_chn");
        writer.addHeaderAlias("categoryname", "categoryname");
        writer.addHeaderAlias("queryinput", "queryinput");
        writer.addHeaderAlias("deviceinfo_imsi", "deviceinfo_imsi");
        writer.addHeaderAlias("deviceinfo_sn", "deviceinfo_sn");
        writer.addHeaderAlias("deviceinfo_uuid", "deviceinfo_uuid");
        writer.addHeaderAlias("ivi_city", "ivi_city");
        writer.addHeaderAlias("ivi_province", "ivi_province");
        writer.addHeaderAlias("poi_city", "poi_city");
        writer.addHeaderAlias("poi_province", "poi_province");
        writer.addHeaderAlias("search_cnt", "search_cnt");
        writer.addHeaderAlias("stay_cnt", "stay_cnt");
        writer.addHeaderAlias("navi_cnt", "navi_cnt");
        writer.addHeaderAlias("total_cnt", "total_cnt");
        writer.addHeaderAlias("data_type", "data_type");

        writer.addHeaderAlias("zid", "zid");
        writer.addHeaderAlias("vin", "vin");
        writer.addHeaderAlias("is_home", "is_home");
        writer.addHeaderAlias("is_company", "is_company");
        writer.addHeaderAlias("is_collection", "is_collection");

        writer.setOnlyAlias(true);
        writer.write(datas, true);

        File file = new File("E:\\文档\\1、cosmo\\out\\data" + DateUtil.format(new Date(), "yyyyMMddHHmmSS.sss") + ".xlsx");
        if(!file.exists()){
            file.createNewFile();
        }
        FileOutputStream fileOutputStream = new FileOutputStream(file);
        writer.flush(fileOutputStream);

        writer.close();
    }

    public static void main(String[] args) throws IOException {
        //待输出的结构
        ArrayList<Map<String, Object>> result = new ArrayList<>();

        /***读取excel**/
        List<Map<String, Object>> datas = readExcel();

//        String data = null;
//        try {
//            data = org.apache.commons.io.FileUtils.readFileToString(new File("E:\\文档\\1、cosmo\\vin-poi-test.json"));
////            data = org.apache.commons.io.FileUtils.readFileToString(new File("E:\\文档\\1、cosmo\\vin-poi.json"));
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//        JSONArray datas = JSON.parseArray(data);
        for (int i = 0; i < datas.size(); i++) {
            Map<String, Object> item = datas.get(i);
            String poi_id = (String)item.get("poiid");
            String vin = (String)item.get("deviceinfo_sn");
            //根据vin查
            JSONObject jsonObject = queryPoiInfoByVin(vin, poi_id);
            item.putAll(jsonObject);

            try {
                TimeUnit.MILLISECONDS.sleep(RandomUtils.nextInt(50, 100));
            } catch (InterruptedException e) {
                e.printStackTrace();
            }

            result.add(item);
        }

        /*************输出*************/
        writerExcel(result);
    }
}
